import pandas as pd

from app.model_phl import utils
from app.model_phl import util_log


def get_mysql_json_url(apply_no: str):
    """
    从业务数据库：获取华为通数据
    :param apply_no:订单号
    """
    sql = f'SELECT `BindingFileUrl`.`FileUrl` FROM `BindingFileUrl` WHERE `BindingFileUrl`.`ApplyNO` = "{apply_no}";'
    try:
        # util_log.get_logger().info(sql)
        db_df = utils.select_sql_mysql_v1(sql)
        return db_df.get('FileUrl').loc[0] if len(db_df) else None
    except Exception as e:
        util_log.get_logger().error(e)
        return None


def get_mysql_order(apply_no: str):
    """
    从业务数据库：获取订单数据
    :param apply_no:订单号
    """
    sql = f'SELECT * FROM `SysJkApply` WHERE `SysJkApply`.`ApplyNO` = "{apply_no}" LIMIT 1;'
    try:
        # util_log.get_logger().info(sql)
        db_df = utils.select_sql_mysql_v1(sql)
        if db_df is None:
            return None

        return {
            "apply_no": apply_no,
            "apply_date": pd.Timestamp(db_df.get('ApplyDate').loc[0]),
            "app_id": db_df.get('AppId').loc[0],
            "phone": db_df.get('Phone').loc[0],
            "user_id": db_df.get('UserId').loc[0],
            "project_id": db_df.get('ProjectId').loc[0],
        }

    except Exception as e:
        util_log.get_logger().error(e)
        return None


def get_mysql_social_connection(user_id: str):
    """
    从业务数据库：获取社会关系数据
    :param user_id: 客户id
    """
    sql = f'SELECT * FROM `SysSocialConnections` WHERE `SysSocialConnections`.`UserId` = "{user_id}" LIMIT 1; '
    try:
        # util_log.get_logger().info(sql)
        return utils.select_sql_mysql_v1(sql)
    except Exception as e:
        util_log.get_logger().error(e)
        return None


def get_mysql_dimension(apply_no: str):
    """
    从业务数据库：获取订单的风控参数
    :param apply_no:订单号
    """
    sql = f"""
    SELECT
        * 
    FROM
        `fk_qll_dimension_list`
        INNER JOIN fk_qll_dimension_info ON fk_qll_dimension_info.ApplyNO = fk_qll_dimension_list.ApplyNO 
    WHERE
        `fk_qll_dimension_list`.`ApplyNO` = "{apply_no}" 
        LIMIT 1
    """
    try:
        # util_log.get_logger().info(sql)
        db_df = utils.select_sql_mysql_v1(sql)
        return db_df.iloc[0] if len(db_df) else None
    except Exception as e:
        util_log.get_logger().error(e)
        return None


def get_mysql_last_refuse001(phone, apply_date):
    """
    与上次拒绝时间间隔:last_refuse001
    :param phone:用户手机号
    :param apply_date:订单申请时间
    """
    # 审核状态（IsCheckedState） 0未知 1待审核 2初审通过 3初审拒绝 4 终审通过 5终审拒绝 6主管直批 7 财务拒绝 8 初审退回 9终审退回
    sql = f"""
    SELECT
        DATEDIFF( '{apply_date}', SysJkApply.IsCheckedDate ) AS last_refuse001 
    FROM
        SysJkApply 
    WHERE
        SysJkApply.IsCheckedDate IS NOT NULL 
        AND SysJkApply.IsCheckedState IN ( 3, 5, 7, 8, 9 ) 
        AND SysJkApply.Phone = '{phone}' 
        AND SysJkApply.ApplyDate < '{apply_date}' 
    ORDER BY
        SysJkApply.ApplyDate DESC 
        LIMIT 1
    """
    try:
        # util_log.get_logger().info(sql)
        db_df = utils.select_sql_mysql_v1(sql)
        return db_df.get('last_refuse001').loc[0] if len(db_df) else None
    except Exception as e:
        util_log.get_logger().error(e)
        return None


def get_mysql_register_cnt(
        apply_date,
        app_id,
        phone,
        days=0,
        imei='-999',
        imsi='-999',
        gaid='-999'
):
    """
    注册账户数(同设备):biz_devRegCnt、biz_devRegCnt_1d、biz_devRegCnt_1w、biz_devRegCnt_1m
    """
    where = f" tmp.days <= {days}" if days else f"tmp.days >= 0 "
    sql = f"""
    SELECT
        COUNT( tmp.Id ) AS cnt 
    FROM
        (
        SELECT
            member.Id,
            DATEDIFF('{apply_date}' , member.RegistreDate ) AS `days` 
        FROM
            SysUserMember AS member
            INNER JOIN (
                ( SELECT MemberPhone, imei, imsi, gaid FROM Sysdevicedates_0 ) UNION
                ( SELECT MemberPhone, imei, imsi, gaid FROM Sysdevicedates_1 ) UNION
                ( SELECT MemberPhone, imei, imsi, gaid FROM Sysdevicedates_2 ) UNION
                ( SELECT MemberPhone, imei, imsi, gaid FROM Sysdevicedates_3 ) UNION
                ( SELECT MemberPhone, imei, imsi, gaid FROM Sysdevicedates_4 ) UNION
                ( SELECT MemberPhone, imei, imsi, gaid FROM Sysdevicedates_5 ) UNION
                ( SELECT MemberPhone, imei, imsi, gaid FROM Sysdevicedates_6 ) UNION
                ( SELECT MemberPhone, imei, imsi, gaid FROM Sysdevicedates_7 ) UNION
                ( SELECT MemberPhone, imei, imsi, gaid FROM Sysdevicedates_8 ) UNION
                ( SELECT MemberPhone, imei, imsi, gaid FROM Sysdevicedates_9 ) 
            ) AS device ON member.Phone = device.MemberPhone 
        WHERE
            member.RegisterSource = '{app_id}' 
            AND member.RegistreDate <= '{apply_date}' 
            -- AND member.Phone = '{phone}' 
            AND ( 
                device.imei = '{imei}' 
                OR device.imsi = '{imsi}' 
                OR device.gaid = '{gaid}' 
            ) 
        ) AS tmp 
    WHERE 
        {where}
        LIMIT 1
    """
    try:
        # util_log.get_logger().info(sql)
        db_df = utils.select_sql_mysql_v1(sql)
        return db_df.get('cnt').loc[0] if len(db_df) else None
    except Exception as e:
        util_log.get_logger().error(e)
        return None


def get_mysql_register_ip_cnt(apply_date, app_id, days, ip):
    """
    注册账户数（同Ip）:biz_ipRegCnt、biz_ipRegCnt_1d
    """
    where = f" tmp.days <= {days}" if days else f"tmp.days >= 0 "
    sql = f"""
    SELECT
        COUNT( tmp.id ) AS cnt 
    FROM
        (
        SELECT
            member.Id AS id,
            DATEDIFF('{apply_date}', member.RegistreDate ) AS `days` 
        FROM
            SysUserMember AS member
            INNER JOIN (
                ( SELECT MemberPhone, ip FROM Sysdevicedates_0 ) UNION
                ( SELECT MemberPhone, ip FROM Sysdevicedates_1 ) UNION
                ( SELECT MemberPhone, ip FROM Sysdevicedates_2 ) UNION
                ( SELECT MemberPhone, ip FROM Sysdevicedates_3 ) UNION
                ( SELECT MemberPhone, ip FROM Sysdevicedates_4 ) UNION
                ( SELECT MemberPhone, ip FROM Sysdevicedates_5 ) UNION
                ( SELECT MemberPhone, ip FROM Sysdevicedates_6 ) UNION
                ( SELECT MemberPhone, ip FROM Sysdevicedates_7 ) UNION
                ( SELECT MemberPhone, ip FROM Sysdevicedates_8 ) UNION
                ( SELECT MemberPhone, ip FROM Sysdevicedates_9 ) 
            ) AS device ON member.Phone = device.MemberPhone 
        WHERE
            member.RegisterSource = '{app_id}' 
            AND member.RegistreDate < '{apply_date}'
            AND device.ip = '{ip}' 
        ) AS tmp 
    WHERE 
        {where}
        LIMIT 1
        """
    try:
        # util_log.get_logger().info(sql)
        db_df = utils.select_sql_mysql_v1(sql)
        return db_df.get('cnt').loc[0] if len(db_df) else None
    except Exception as e:
        util_log.get_logger().error(e)
        return None


def get_mysql_loan_cnt(
        apply_date,
        app_id,
        phone,
        imei='-999',
        imsi='-999',
        ip='-999',
        gaid='-999',
):
    """
    在贷单数(同设备):biz_devLoanCnt
    """
    sql = f"""
    SELECT
        COUNT( repay.ApplyNO ) AS cnt 
    FROM
        SysRePayment AS repay
        INNER JOIN ( SELECT RegisterSource, Phone, Id FROM SysUserMember ) member ON member.Id = repay.MemberId
        INNER JOIN ( 
            ( SELECT ApplyNO, ip, imei, imsi, gaid FROM Sysdevicedates_0 ) UNION
            ( SELECT ApplyNO, ip, imei, imsi, gaid FROM Sysdevicedates_1 ) UNION
            ( SELECT ApplyNO, ip, imei, imsi, gaid FROM Sysdevicedates_2 ) UNION
            ( SELECT ApplyNO, ip, imei, imsi, gaid FROM Sysdevicedates_3 ) UNION
            ( SELECT ApplyNO, ip, imei, imsi, gaid FROM Sysdevicedates_4 ) UNION
            ( SELECT ApplyNO, ip, imei, imsi, gaid FROM Sysdevicedates_5 ) UNION
            ( SELECT ApplyNO, ip, imei, imsi, gaid FROM Sysdevicedates_6 ) UNION
            ( SELECT ApplyNO, ip, imei, imsi, gaid FROM Sysdevicedates_7 ) UNION
            ( SELECT ApplyNO, ip, imei, imsi, gaid FROM Sysdevicedates_8 ) UNION
            ( SELECT ApplyNO, ip, imei, imsi, gaid FROM Sysdevicedates_9 ) 
        ) AS device ON repay.ApplyNO = device.ApplyNO 
    WHERE
        repay.State <> 3
        AND repay.ApplyDate < '{apply_date}' 
        AND member.RegisterSource = '{app_id}' 
        AND (
            member.Phone = '{phone}' 
            OR device.imei = '{imei}' 
            OR device.imsi = '{imsi}' 
            OR device.ip = '{ip}' 
            OR device.gaid = '{gaid}' 
        )
        LIMIT 1
    """
    try:
        # util_log.get_logger().info(sql)
        db_df = utils.select_sql_mysql_v1(sql)
        return db_df.get('cnt').loc[0] if len(db_df) else None
    except Exception as e:
        util_log.get_logger().error(e)
        return None


def get_mysql_overdue_cnt(
        apply_date,
        app_id,
        phone,
        imei='-999',
        imsi='-999',
        ip='-999',
        gaid='-999',
):
    """
    逾期订单数:biz_black_GidDevice
    """
    sql = f"""
    SELECT
        COUNT( repay.ApplyNO ) AS cnt 
    FROM
        SysRePayment AS repay
        INNER JOIN ( SELECT RegisterSource, Phone, Id FROM SysUserMember ) member ON member.Id = repay.MemberId
        INNER JOIN ( 
            ( SELECT ApplyNO, ip, imei, imsi, gaid FROM Sysdevicedates_0 ) UNION
            ( SELECT ApplyNO, ip, imei, imsi, gaid FROM Sysdevicedates_1 ) UNION
            ( SELECT ApplyNO, ip, imei, imsi, gaid FROM Sysdevicedates_2 ) UNION
            ( SELECT ApplyNO, ip, imei, imsi, gaid FROM Sysdevicedates_3 ) UNION
            ( SELECT ApplyNO, ip, imei, imsi, gaid FROM Sysdevicedates_4 ) UNION
            ( SELECT ApplyNO, ip, imei, imsi, gaid FROM Sysdevicedates_5 ) UNION
            ( SELECT ApplyNO, ip, imei, imsi, gaid FROM Sysdevicedates_6 ) UNION
            ( SELECT ApplyNO, ip, imei, imsi, gaid FROM Sysdevicedates_7 ) UNION
            ( SELECT ApplyNO, ip, imei, imsi, gaid FROM Sysdevicedates_8 ) UNION
            ( SELECT ApplyNO, ip, imei, imsi, gaid FROM Sysdevicedates_9 ) 
        ) AS device ON repay.ApplyNO = device.ApplyNO 
    WHERE
        repay.State <> 3
        AND repay.Overduedays > 0 
        AND repay.ApplyDate < '{apply_date}' 
        AND member.RegisterSource = '{app_id}' 
        AND (
            member.Phone = '{phone}' 
            OR device.imei = '{imei}' 
            OR device.imsi = '{imsi}' 
            OR device.ip = '{ip}' 
            OR device.gaid = '{gaid}' 
        )
        LIMIT 1
    """
    try:
        # util_log.get_logger().info(sql)
        db_df = utils.select_sql_mysql_v1(sql)
        return db_df.get('cnt').loc[0] if len(db_df) else None
    except Exception as e:
        util_log.get_logger().error(e)
        return None


def get_mysql_fbid_cnt(user_id, app_id, apply_date):
    """
    同一facebook id累计用户数:biz_fbid_cnt
    """
    sql = f"""
    SELECT
        COUNT( `info`.`MemberId` ) AS cnt 
    FROM
        `SysBasicInformation` AS info
        INNER JOIN ( SELECT Id, RegisterSource FROM SysUserMember ) AS member ON member.Id = info.MemberId 
    WHERE
        `info`.CreatDate < '{apply_date}'
        AND `info`.`Facebook` IN (SELECT `Facebook` FROM `SysBasicInformation` WHERE `MemberId` = '{user_id}')
        AND `member`.`RegisterSource` = '{app_id}'
        LIMIT 1
    """
    try:
        # util_log.get_logger().info(sql)
        db_df = utils.select_sql_mysql_v1(sql)
        return db_df.get('cnt').loc[0] if len(db_df) else None
    except Exception as e:
        util_log.get_logger().error(e)
        return None


def get_mysql_bankcard_phone_cnt(user_id, apply_date):
    """
    同一银行卡号关联不同手机号个数:bankcard_associated_phone_cnt
    :param user_id:
    :param apply_date:
    """
    sql = f"""
    SELECT
        COUNT( DISTINCT member.Phone ) AS cnt 
    FROM
        `SysUserMember` AS member
        INNER JOIN ( SELECT Id, BankCard, UserId FROM SysBankcardAuthentication ) AS bank ON member.Id = bank.UserId 
    WHERE
        member.RegistreDate < '{apply_date}' 
        AND bank.BankCard IN (SELECT BankCard FROM SysBankcardAuthentication WHERE UserId = '{user_id}')
        LIMIT 1
    """
    try:
        # util_log.get_logger().info(sql)
        db_df = utils.select_sql_mysql_v1(sql)
        return db_df.get('cnt').loc[0] if len(db_df) else None
    except Exception as e:
        util_log.get_logger().error(e)
        return None


def get_mysql_social_connections(user_id):
    """
    获取用户联系人数据
    四个亲密联系人中majorfraud号码的个数:major_phone_majorfraud_cnt
    """
    sql = f"""
    SELECT
        * 
    FROM
        `SysSocialConnections` 
    WHERE
        UserId = '{user_id}' 
        LIMIT 1
    """
    try:
        # util_log.get_logger().info(sql)
        db_df = utils.select_sql_mysql_v1(sql)
        return db_df.loc[0] if len(db_df) else None
    except Exception as e:
        util_log.get_logger().error(e)
        return None


def get_mysql_inner_black_phone_cnt(phone, apply_date):
    """
    手机号命中黑名数:inner_black_phone
    """
    sql = f"""
    SELECT
        count(DISTINCT Phone ) AS cnt 
    FROM
        BWGlist 
    WHERE
        Phone = '{phone}' 
        AND statu = 2 
        AND CreatDate <= '{apply_date}' 
        LIMIT 1
    """
    try:
        # util_log.get_logger().info(sql)
        db_df = utils.select_sql_mysql_v1(sql)
        return db_df.get('cnt').loc[0] if len(db_df) else None
    except Exception as e:
        util_log.get_logger().error(e)
        return None


def get_mysql_inner_black_card_cnt(idcard, apply_date):
    """
    身份证命中黑名单数:inner_black_idCard
    """
    sql = f"""
    SELECT
        count( phone ) AS cnt 
    FROM
        BWGlist 
    WHERE
        statu = 2 
        AND CreatDate <= '{apply_date}' 
        AND idCard = '{idcard}'
        LIMIT 1
    """
    try:
        # util_log.get_logger().info(sql)
        db_df = utils.select_sql_mysql_v1(sql)
        return db_df.get('cnt').loc[0] if len(db_df) else None
    except Exception as e:
        util_log.get_logger().error(e)
        return None


def get_mysql_cur_default_cnt(phone, app_id, apply_date):
    """
    前是否处于逾期中（是：1，否：0）:biz_curDefault
    """
    sql = f"""
    SELECT
        COUNT( repay.ApplyNO ) cnt 
    FROM
        SysRePayment AS repay
        INNER JOIN ( SELECT Id, RegisterSource, Phone, RegistreDate FROM SysUserMember ) AS member 
        ON member.Id = repay.MemberId 
    WHERE
        repay.Overduedays > 0 
        AND repay.State <> 3 
        AND repay.ApplyDate < '{apply_date}'
        AND member.RegisterSource = '{app_id}' 
        AND member.Phone = '{phone}'
        LIMIT 1
    """
    try:
        # util_log.get_logger().info(sql)
        db_df = utils.select_sql_mysql_v1(sql)
        return db_df.get('cnt').loc[0] if len(db_df) else None
    except Exception as e:
        util_log.get_logger().error(e)
        return None


def get_mysql_maxDpd(phone, app_id, apply_date):
    """
    历史最大逾期天数:biz_maxDpd
    """
    sql = f"""
    SELECT
        MAX( Overduedays ) AS days 
    FROM
        SysRePayment AS repay
        INNER JOIN ( SELECT Id, RegisterSource, Phone, RegistreDate FROM SysUserMember ) AS member 
        ON member.Id = repay.MemberId 
    WHERE
        repay.Overduedays > 0 
        AND repay.ApplyDate < '{apply_date}'
        AND member.RegisterSource = '{app_id}' 
        AND member.Phone = '{phone}'
        LIMIT 1
    """
    try:
        # util_log.get_logger().info(sql)
        db_df = utils.select_sql_mysql_v1(sql)
        return db_df.get('days').loc[0] if len(db_df) else None
    except Exception as e:
        util_log.get_logger().error(e)
        return None


def get_mysql_lastOrderDpd(phone, app_id, apply_date):
    """
    最近一笔结清交易逾期天数:biz_lastOrderDpd
    """
    sql = f"""
    SELECT
        Overduedays AS days 
    FROM
        SysRePayment AS repay
        INNER JOIN ( SELECT Id, RegisterSource, Phone, RegistreDate FROM SysUserMember ) AS member 
        ON member.Id = repay.MemberId 
    WHERE
        repay.Overduedays > 0 
        AND repay.State = 3 
        AND repay.ApplyDate < '{apply_date}'
        AND member.RegisterSource = '{app_id}' 
        AND member.Phone = '{phone}'
    ORDER BY
        ReDate DESC 
        LIMIT 1
    """
    try:
        # util_log.get_logger().info(sql)
        db_df = utils.select_sql_mysql_v1(sql)
        return db_df.get('days').loc[0] if len(db_df) else None
    except Exception as e:
        util_log.get_logger().error(e)
        return None


def get_mysql_whitelist_first_time_diff_days(phone, apply_date):
    """
    白名单首次日期距离申请时间天数校验:whitelist_first_time_diff_days
    """
    sql = f"""
    SELECT
        DATEDIFF( '{apply_date}', SysWhitelist.CreatDate ) AS days 
    FROM
        SysWhitelist 
    WHERE
        SysWhitelist.Phone = '{phone}' 
        AND SysWhitelist.CreatDate < '{apply_date}' 
    ORDER BY
        SysWhitelist.CreatDate 
        LIMIT 1
    """
    try:
        # util_log.get_logger().info(sql)
        db_df = utils.select_sql_mysql_v1(sql)
        return db_df.get('days').loc[0] if len(db_df) else None
    except Exception as e:
        util_log.get_logger().error(e)
        return None


def get_mysql_week_number_of_loans(phone, apply_no, start_date, end_date):
    """
    本平台近一周该客户放款笔数:week_number_of_loans
    """
    sql = f"""
    SELECT
        COUNT( DISTINCT SysRePayment.ApplyNO ) AS cnt 
    FROM
        SysRePayment 
    WHERE
        SysRePayment.ApplyNO <> '{apply_no}' 
        AND SysRePayment.ApplyDate > '{start_date}' 
        AND SysRePayment.ApplyDate < '{end_date}' 
        AND SysRePayment.MemberId IN (SELECT SysUserMember.Id FROM SysUserMember WHERE SysUserMember.Phone = '{phone}') 
        LIMIT 1
    """
    try:
        # util_log.get_logger().info(sql)
        db_df = utils.select_sql_mysql_v1(sql)
        return db_df.get('cnt').loc[0] if len(db_df) else None
    except Exception as e:
        util_log.get_logger().error(e)
        return None


def get_mysql_now_overdue_cnt(user_id, app_id, apply_no, apply_date):
    """
    本平台当前逾期情况:now_overdue_num
    """
    sql = f"""
    SELECT
        COUNT( DISTINCT SysRePayment.ApplyNO ) AS cnt 
    FROM
        SysRePayment 
    WHERE
        SysRePayment.State = 2 
        AND SysRePayment.MemberId = '{user_id}' 
        AND SysRePayment.AppId = '{app_id}' 
        AND SysRePayment.Overduedays > 0 
        AND SysRePayment.ApplyDate < '{apply_date}' 
        AND SysRePayment.ApplyNO <> '{apply_no}' 
        LIMIT 1
    """
    try:
        # util_log.get_logger().info(sql)
        db_df = utils.select_sql_mysql_v1(sql)
        return db_df.get('cnt').loc[0] if len(db_df) else None
    except Exception as e:
        util_log.get_logger().error(e)
        return None


def get_mysql_max_overdue_days(phone, app_id, apply_date):
    """
    已结清订单逾期天数最大值:max_overdue_days
    """
    sql = f"""
    SELECT
        max(repay.Overduedays) AS days 
    FROM
        SysRePayment AS repay
        INNER JOIN ( SELECT Id, RegisterSource, Phone, RegistreDate FROM SysUserMember ) AS member 
        ON member.Id = repay.MemberId 
    WHERE
        repay.Overduedays > 0 
        AND repay.State = 3 
        AND repay.ApplyDate < '{apply_date}'
        AND member.RegisterSource = '{app_id}' 
        AND member.Phone = '{phone}'
        LIMIT 1
    """
    try:
        # util_log.get_logger().info(sql)
        db_df = utils.select_sql_mysql_v1(sql)
        return db_df.get('days').loc[0] if len(db_df) else None
    except Exception as e:
        util_log.get_logger().error(e)
        return None


def get_mysql_loan_not_yet_cnt(project_id, user_id, apply_date):
    """
    当前产品的在贷未还笔数:on_loan_not_yet_cnt
    """
    sql = f"""
    SELECT
        COUNT( DISTINCT SysRePayment.ApplyNO ) AS cnt 
    FROM
        SysRePayment 
    WHERE
        SysRePayment.State <> 3 
        AND SysRePayment.MemberId = '{user_id}' 
        AND SysRePayment.ProjectId = '{project_id}' 
        AND SysRePayment.Overduedays > 0 
        AND SysRePayment.ApplyDate < '{apply_date}' 
        LIMIT 1
    """
    try:
        # util_log.get_logger().info(sql)
        db_df = utils.select_sql_mysql_v1(sql)
        return db_df.get('cnt').loc[0] if len(db_df) else None
    except Exception as e:
        util_log.get_logger().error(e)
        return None


def get_mysql_week_apply_num(user_id, app_id, apply_no, start_date, end_date):
    """
    本平台近一周内申请订单数:week_apply_num
    """
    sql = f"""
    SELECT
        COUNT( DISTINCT SysJkApply.ApplyNO ) AS cnt 
    FROM
        SysJkApply 
    WHERE
        SysJkApply.ApplyNO <> '{apply_no}' 
        AND SysJkApply.UserId = '{user_id}' 
        AND SysJkApply.AppId = '{app_id}' 
        AND SysJkApply.ApplyDate > '{start_date}' 
        AND SysJkApply.ApplyDate < '{end_date}' 
        LIMIT 1
    """
    try:
        # util_log.get_logger().info(sql)
        db_df = utils.select_sql_mysql_v1(sql)
        return db_df.get('cnt').loc[0] if len(db_df) else None
    except Exception as e:
        util_log.get_logger().error(e)
        return None


def get_mysql_max_expire_day_all_phone_cur(phone, apply_date):
    """
    全平台当前未还交易逾期天数（基于注册号码）:biz_max_expire_day_all_phone_cur
    1待还款 2已逾期 3已结清
    """
    sql = f"""
    SELECT
        MAX( Overduedays ) AS days 
    FROM
        SysRePayment AS repay
        INNER JOIN ( SELECT Id, Phone, ApplyDate, ApplyNO FROM SysJkApply ) AS apply ON apply.ApplyNO = repay.ApplyNO 
    WHERE
        repay.Overduedays > 0 
        AND repay.State = 1 
        AND apply.Phone = '{phone}' 
        AND apply.ApplyDate < '{apply_date}' 
        LIMIT 1
    """
    try:
        # util_log.get_logger().info(sql)
        db_df = utils.select_sql_mysql_v1(sql)
        return db_df.get('days').loc[0] if len(db_df) else None
    except Exception as e:
        util_log.get_logger().error(e)
        return None


def get_mysql_max_expire_day_all_phone_history(phone, apply_date):
    """
    全平台最大逾期天数（基于注册号码）:biz_max_expire_day_all_phone_history
    """
    sql = f"""
    SELECT
        MAX( Overduedays ) AS days 
    FROM
        SysRePayment AS repay
        INNER JOIN ( SELECT Id, Phone, ApplyDate, ApplyNO FROM SysJkApply ) AS apply ON apply.ApplyNO = repay.ApplyNO 
    WHERE
        repay.Overduedays > 0 
        AND apply.Phone = '{phone}' 
        AND apply.ApplyDate < '{apply_date}' 
        LIMIT 1
    """
    try:
        # util_log.get_logger().info(sql)
        db_df = utils.select_sql_mysql_v1(sql)
        return db_df.get('days').loc[0] if len(db_df) else None
    except Exception as e:
        util_log.get_logger().error(e)
        return None


def get_mysql_max_expire_day_all_real_idcard_history(idcard, apply_date):
    """
    全平台最大逾期天数（基于最终身份证号）:biz_max_expire_day_all_real_idcard_history
    """
    sql = f"""
    SELECT
        MAX( Overduedays ) AS days 
    FROM
        SysRePayment AS repay
        INNER JOIN ( SELECT ApplyDate, ApplyNO FROM SysJkApply ) AS apply ON apply.ApplyNO = repay.ApplyNO
        INNER JOIN ( SELECT UserId, Card FROM SysCardAuthentication ) AS card ON card.UserId = repay.MemberId 
    WHERE
        apply.ApplyDate < '{apply_date}' 
        AND card.Card = '{idcard}'
        LIMIT 1
    """
    try:
        # util_log.get_logger().info(sql)
        db_df = utils.select_sql_mysql_v1(sql)
        return db_df.get('days').loc[0] if len(db_df) else None
    except Exception as e:
        util_log.get_logger().error(e)
        return None


def get_mysql_idcard(user_id):
    """
    获取用户省份证证号
    """
    sql = f"""
    SELECT
        Card
    FROM
        SysCardAuthentication
    WHERE
        UserId = '{user_id}'
        LIMIT 1
    """
    try:
        # util_log.get_logger().info(sql)
        db_df = utils.select_sql_mysql_v1(sql)
        return db_df.get('Card').loc[0] if len(db_df) else None
    except Exception as e:
        util_log.get_logger().error(e)
        return None